import arrow
import openpyxl
from Taxmanage.jx_import import *
from Taxmanage.xx_concat import *
from Taxmanage.zz_import import *
from Taxmanage.erp_rebot import *


def last_data():

    t1 = pd.read_excel(last_path, sheet_name='附表1', skiprows=8, dtype='str')
    t1 = t1.iloc[:, :-3]
    t4 = pd.read_excel(last_path, sheet_name='附表4', skiprows=12, dtype='str')
    t5 = pd.read_excel(last_path, sheet_name='附表5', skiprows=5, dtype='str')
    workbook = load_workbook(tax_path)
    worksheet = workbook['附表1']
    worksheet.cell(row=22, column=4, value=t1['本月数'].values[19])
    workbook.save(tax_path)
    workbook.close()
    # a = r'C:\Users\31406\AppData\Local\Programs\Python\Python37\Scripts\'
    workbook = load_workbook(tax_path)
    worksheet = workbook['附表4']
    worksheet.cell(row=15, column=3, value=t4['期末余额'].values[1])
    workbook.save(tax_path)
    workbook.close()

    workbook = load_workbook(tax_path)
    worksheet = workbook['附表5']
    worksheet.cell(row=11, column=5, value=t5['期末余额'].values[2])
    workbook.save(tax_path)
    worksheet.cell(row=12, column=5, value=t5['期末余额'].values[3])
    workbook.save(tax_path)
    workbook.close()


def main():
    last_data()
    jximport(jx_path, tax_path)
    # zz_erp()
    time.sleep(2)
    xximport(xx_path, tax_path)
    time.sleep(2)
    zzimport(nowdate, tax_path)


# 时间
now = arrow.now()
nowdate = now.shift(months=-1).format('YYYY-MM')
lastdate = now.shift(months=-2).format('YYYY-MM')
jx_path = 'D:/税务机器人/' + nowdate + '/进项发票/发票认证清单.xls'
xx_path = 'D:/税务机器人/' + nowdate + '/销项发票/'
zz_path = 'D:/税务机器人/' + nowdate + '/总账数据/CUX_科目按来源展示.xlsx'
tax_path = 'D:/税务机器人/' + nowdate + '/财辅系统纳税.xlsx'
last_path = 'D:/税务机器人/' + lastdate + '/财辅系统纳税.xlsx'
# 判断文件夹是否存在
nextdate = now.shift(months=0).format('YYYY-MM')
if os.path.exists('D:/税务机器人/' + nextdate):
    pass
else:
    os.mkdir('D:/税务机器人/' + nextdate)
if os.path.exists('D:/税务机器人/' + nextdate + '/销项发票/'):
    pass
else:
    os.mkdir('D:/税务机器人/' + nextdate + '/销项发票/')
if os.path.exists('D:/税务机器人/' + nextdate + '/进项发票/'):
    pass
else:
    os.mkdir('D:/税务机器人/' + nextdate + '/进项发票/')
if os.path.exists('D:/税务机器人/' + nextdate + '/总账数据/'):
    pass
else:
    os.mkdir('D:/税务机器人/' + nextdate + '/总账数据/')

if os.path.exists(tax_path):
    pass
else:
    shutil.copy('D:/税务机器人/基础设置/纳税模板.xlsx', tax_path)

if os.path.exists(jx_path):
    lists = os.listdir(xx_path)
    if len(lists) != 0:
        main()
    else:
        print('缺少销项发票文件')
else:
    print('缺少进项发票认证文件')





